package com.hotel.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hotel.entity.RoomType;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 房间类型Mapper接口
 */
@Mapper
public interface RoomTypeMapper extends BaseMapper<RoomType> {
    
    /**
     * 获取房间类型统计信息
     */
    @Select("SELECT " +
            "COUNT(DISTINCT rt.id) as total_room_types, " +
            "COUNT(r.id) as total_rooms, " +
            "SUM(CASE WHEN r.status = 'available' THEN 1 ELSE 0 END) as available_rooms, " +
            "SUM(CASE WHEN r.status = 'occupied' THEN 1 ELSE 0 END) as occupied_rooms, " +
            "AVG(rt.base_price) as avg_price " +
            "FROM room_type rt " +
            "LEFT JOIN room r ON rt.id = r.room_type_id " +
            "WHERE rt.status = 'enabled'")
    Map<String, Object> getRoomTypeStats();
    
    /**
     * 获取房间类型房间数量分布
     */
    @Select("SELECT rt.id, rt.name, rt.code, rt.base_price, " +
            "COUNT(r.id) as total_rooms, " +
            "SUM(CASE WHEN r.status = 'available' THEN 1 ELSE 0 END) as available_rooms, " +
            "SUM(CASE WHEN r.status = 'occupied' THEN 1 ELSE 0 END) as occupied_rooms, " +
            "SUM(CASE WHEN r.status = 'cleaning' THEN 1 ELSE 0 END) as cleaning_rooms, " +
            "SUM(CASE WHEN r.status = 'maintenance' THEN 1 ELSE 0 END) as maintenance_rooms, " +
            "SUM(CASE WHEN r.status = 'out_of_order' THEN 1 ELSE 0 END) as out_of_order_rooms, " +
            "CASE WHEN COUNT(r.id) > 0 THEN " +
            "  ROUND(SUM(CASE WHEN r.status = 'occupied' THEN 1 ELSE 0 END) * 100.0 / COUNT(r.id), 2) " +
            "ELSE 0 END as occupancy_rate " +
            "FROM room_type rt " +
            "LEFT JOIN room r ON rt.id = r.room_type_id " +
            "WHERE rt.status = 'enabled' " +
            "GROUP BY rt.id, rt.name, rt.code, rt.base_price " +
            "ORDER BY rt.sort_order, rt.name")
    List<Map<String, Object>> getRoomTypeRoomCount();
    
    /**
     * 检查房间类型是否被使用
     */
    @Select("SELECT COUNT(*) FROM room WHERE room_type_id = #{roomTypeId}")
    int countRoomsByRoomType(@Param("roomTypeId") Long roomTypeId);
    
    /**
     * 获取房间类型价格历史（模拟数据，实际应该有价格历史表）
     */
    @Select("SELECT rt.base_price as price, rt.update_time as price_date, 'current' as price_type " +
            "FROM room_type rt " +
            "WHERE rt.id = #{roomTypeId} " +
            "ORDER BY rt.update_time DESC " +
            "LIMIT 10")
    List<Map<String, Object>> getRoomTypePriceHistory(@Param("roomTypeId") Long roomTypeId);
    
    /**
     * 获取房间类型收入统计
     */
    @Select("SELECT rt.id, rt.name, rt.code, " +
            "COUNT(res.id) as total_reservations, " +
            "COALESCE(SUM(res.total_amount), 0) as total_revenue, " +
            "CASE WHEN COUNT(res.id) > 0 THEN " +
            "  ROUND(SUM(res.total_amount) / COUNT(res.id), 2) " +
            "ELSE 0 END as avg_daily_rate " +
            "FROM room_type rt " +
            "LEFT JOIN room r ON rt.id = r.room_type_id " +
            "LEFT JOIN reservation res ON r.id = res.room_id AND res.status = 'checkedout' " +
            "WHERE rt.status = 'enabled' " +
            "GROUP BY rt.id, rt.name, rt.code " +
            "ORDER BY total_revenue DESC")
    List<Map<String, Object>> getRoomTypeRevenueStats();
    
    /**
     * 获取房间类型预订趋势
     */
    @Select("SELECT rt.name as room_type_name, " +
            "DATE_FORMAT(res.create_time, '%Y-%m') as month, " +
            "COUNT(res.id) as reservation_count, " +
            "SUM(res.total_amount) as revenue " +
            "FROM room_type rt " +
            "LEFT JOIN room r ON rt.id = r.room_type_id " +
            "LEFT JOIN reservation res ON r.id = res.room_id " +
            "WHERE rt.status = 'enabled' " +
            "AND res.create_time >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) " +
            "GROUP BY rt.id, rt.name, DATE_FORMAT(res.create_time, '%Y-%m') " +
            "ORDER BY month DESC, rt.name")
    List<Map<String, Object>> getRoomTypeReservationTrend();
    
    /**
     * 获取热门房间类型排行
     */
    @Select("SELECT rt.id, rt.name, rt.code, rt.base_price, " +
            "COUNT(res.id) as reservation_count, " +
            "SUM(res.total_amount) as total_revenue, " +
            "AVG(res.total_amount) as avg_revenue " +
            "FROM room_type rt " +
            "LEFT JOIN room r ON rt.id = r.room_type_id " +
            "LEFT JOIN reservation res ON r.id = res.room_id " +
            "  AND res.status IN ('confirmed', 'checkedin', 'checkedout') " +
            "  AND res.create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) " +
            "WHERE rt.status = 'enabled' " +
            "GROUP BY rt.id, rt.name, rt.code, rt.base_price " +
            "ORDER BY reservation_count DESC, total_revenue DESC " +
            "LIMIT 10")
    List<Map<String, Object>> getPopularRoomTypes();
    
    /**
     * 获取房间类型入住率统计
     */
    @Select("SELECT rt.id, rt.name, rt.code, " +
            "COUNT(DISTINCT r.id) as total_rooms, " +
            "COUNT(res.id) as occupied_nights, " +
            "CASE WHEN COUNT(DISTINCT r.id) > 0 THEN " +
            "  ROUND(COUNT(res.id) * 100.0 / (COUNT(DISTINCT r.id) * #{days}), 2) " +
            "ELSE 0 END as occupancy_rate " +
            "FROM room_type rt " +
            "LEFT JOIN room r ON rt.id = r.room_type_id " +
            "LEFT JOIN reservation res ON r.id = res.room_id " +
            "  AND res.status IN ('checkedin', 'checkedout') " +
            "  AND res.checkin_date >= DATE_SUB(CURDATE(), INTERVAL #{days} DAY) " +
            "WHERE rt.status = 'enabled' " +
            "GROUP BY rt.id, rt.name, rt.code " +
            "ORDER BY occupancy_rate DESC")
    List<Map<String, Object>> getRoomTypeOccupancyRate(@Param("days") int days);
}